﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using WinNemoLib;
using System.Text;
using System.Data.SqlClient;
using System.Data;

namespace TireBias.Models
{
   public class TopCordBomModel
   {
      public class TIRESTDS_DS
      {
         public string _TireIdINF { get; set; }
         public string _TireId { get; set; }
         public string _Sizes { get; set; }
         public string _TireCode { get; set; }
         public string _Forming { get; set; }
         public string _Age { get; set; }
         public string _Ply { get; set; }
         public string _Top { get; set; }
         public string _Tape { get; set; }
         public string _Angle { get; set; }
         public string _Filenm { get; set; }
         public string _SubFd { get; set; }
         public string _FromValid { get; set; }
         public string _ToValid { get; set; }
         public string _ModifyBy { get; set; }
         public string _ModifyDT { get; set; }
         public DateTime _dateF { get; set; }
         public DateTime _dateT { get; set; }
         public string _Width { get; set; }

      }

      public List<TIRESTDS_DS> SEL_TOPCORDBOM(string topcode = "", string size = "", string forming = "",string ply="")
      {
         
         string ConnectionString = Nemo.GetConfiguredDbConnectionWrappertostring("Database");
         SqlConnection cnn = new SqlConnection(ConnectionString);
         StringBuilder str = new StringBuilder();
         string wh = " and ";
         str.AppendLine(" select * from " + Nemo.GetINI("Dbase", "TIRSTD") + ".dbo.TIRSTD a ");
         str.AppendLine(" LEFT JOIN " + Nemo.GetINI("Dbase", "TIRSTD") + ".dbo.TIRSTD_DES b on a.tireid=b.tireid ");
         str.AppendLine(" where getdate() between validfrom and validto ");

         if (StrUtil.IsNotEmpty(topcode))
         {
            str.AppendLine(wh + " tirecode=@code ");
            wh = " and ";
         }
         if (StrUtil.IsNotEmpty(size))
         {
            str.AppendLine(wh + "  [sizes]='" + size + "'"); wh = " and ";
         }
         if (StrUtil.IsNotEmpty(forming))
         {
            str.AppendLine(" and forming='" + forming + "'");
         }
         if (StrUtil.IsNotEmpty(ply))
         {
             str.AppendLine(" and ply='" + ply + "'");
         }

      
         SqlCommand cmd = new SqlCommand(str.ToString(), cnn);
         cmd.Connection.Open();
         cmd.CommandType = CommandType.Text;

         cmd.Parameters.Add("@code", SqlDbType.VarChar).Value = topcode;

         SqlDataAdapter my = new SqlDataAdapter();
         DataSet ds = new DataSet();
         my.SelectCommand = cmd;
         my.Fill(ds);

         List<TIRESTDS_DS> list = new List<TIRESTDS_DS>();
         if (ds.Tables[0].Rows.Count <= 0)
         {
            cmd.Connection.Close();
            return list;
         }
         for (int cuIndex = 0; cuIndex < ds.Tables[0].Rows.Count; cuIndex++)
         {
            DataRow items = ds.Tables[0].Rows[cuIndex];
            TIRESTDS_DS model = new TIRESTDS_DS();
            model._TireCode = items["tirecode"].ToString();
            model._Sizes = items["sizes"].ToString();
            model._Forming = items["forming"].ToString();
            model._Ply = items["ply"].ToString();
            model._Angle = items["angle"].ToString();
            model._SubFd = items["subFd"].ToString();
            model._Filenm = items["filename"].ToString();
            model._Width = items["pwidth"].ToString();
            list.Add(model);
         }
         return list;
      }
    
      public List<TIRESTDS_DS> SEL_TOPCORDBOM_INF(string topcode = "", string size = "", string forming = "", string ply = "")
      {

          string ConnectionString = Nemo.GetConfiguredDbConnectionWrappertostring("Database");
          SqlConnection cnn = new SqlConnection(ConnectionString);
          StringBuilder str = new StringBuilder();
          string wh = " and ";
          str.AppendLine(" select a.*,b.tirecode, [sizes],forming from " + Nemo.GetINI("Dbase", "TIRSTD") + ".dbo.TIRSTD_INF a ");
          str.AppendLine(" LEFT JOIN " + Nemo.GetINI("Dbase", "TIRSTD") + ".dbo.TIRSTD b on a.tireid=b.tireid where a.tireid is not null ");


          if (StrUtil.IsNotEmpty(topcode))
          {
              str.AppendLine(wh + " tirecode=@code ");
              wh = " and ";
          }
          if (StrUtil.IsNotEmpty(size))
          {
              str.AppendLine(wh + "  [sizes]='" + size + "'"); wh = " and ";
          }
          if (StrUtil.IsNotEmpty(forming))
          {
              str.AppendLine(" and forming='" + forming + "'");
          }
  


          SqlCommand cmd = new SqlCommand(str.ToString(), cnn);
          cmd.Connection.Open();
          cmd.CommandType = CommandType.Text;

          cmd.Parameters.Add("@code", SqlDbType.VarChar).Value = topcode;

          SqlDataAdapter my = new SqlDataAdapter();
          DataSet ds = new DataSet();
          my.SelectCommand = cmd;
          my.Fill(ds);

          List<TIRESTDS_DS> list = new List<TIRESTDS_DS>();
          if (ds.Tables[0].Rows.Count <= 0)
          {
              cmd.Connection.Close();
              return list;
          }
          for (int cuIndex = 0; cuIndex < ds.Tables[0].Rows.Count; cuIndex++)
          {
              DataRow items = ds.Tables[0].Rows[cuIndex];
              TIRESTDS_DS model = new TIRESTDS_DS();
              model._TireCode = items["tirecode"].ToString();
              model._Sizes = items["sizes"].ToString();
              model._Forming = items["forming"].ToString();
              model._SubFd = items["subFd"].ToString();
              model._Filenm = items["filename"].ToString();
              list.Add(model);
          }
          return list;
      }

      public string[] LIST_TOPCORDBOM(string topcode = "", string size = "", string forming = "",string sel="")
      {

         string ConnectionString = Nemo.GetConfiguredDbConnectionWrappertostring("Database");
         SqlConnection cnn = new SqlConnection(ConnectionString);
         StringBuilder str = new StringBuilder();
         string wh = " and ";
         str.AppendLine(" select "+ sel +" from " + Nemo.GetINI("Dbase", "TIRSTD") + ".dbo.TIRSTD ");
         str.AppendLine(" where getdate() between validfrom and validto");

         if (StrUtil.IsNotEmpty(topcode))
         {
            str.AppendLine(wh + " tirecode=@code ");
            wh = " and ";
         }
         if (StrUtil.IsNotEmpty(size))
         {
            str.AppendLine(wh + "  [sizes]='" + size + "'"); wh = " and ";
         }
         if (StrUtil.IsNotEmpty(forming))
         {
            str.AppendLine(" and forming='" + forming + "'");
         }


         SqlCommand cmd = new SqlCommand(str.ToString()+" group by "+sel , cnn);
         cmd.Connection.Open();
         cmd.CommandType = CommandType.Text;
         if (StrUtil.IsNotEmpty(topcode))
         {
            cmd.Parameters.Add("@code", SqlDbType.VarChar).Value = topcode;
         }

         SqlDataAdapter my = new SqlDataAdapter();
         DataSet ds = new DataSet();
         my.SelectCommand = cmd;
         my.Fill(ds);

   
         if (ds.Tables[0].Rows.Count <= 0)
         {
            cmd.Connection.Close();
            return null;
         }
         string[] list =new string[ds.Tables[0].Rows.Count];
         for (int cuIndex = 0; cuIndex < ds.Tables[0].Rows.Count; cuIndex++)
         {
            DataRow items = ds.Tables[0].Rows[cuIndex];
            list[cuIndex] = items[sel].ToString();
         }        

         return list;
      }

   }

 

}